/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Assignment.DAO;

import Assignment.DTO.BookDTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author dieptn
 */
public class BookDAO {

    public static ArrayList search(Connection con, BookDTO book) {
        ArrayList<BookDTO> result = new ArrayList<BookDTO>();
        String sql = "";
        PreparedStatement stm = null;
        try {
            if (book == null) {
                sql = "SELECT * FROM BOOKS ORDER BY CAST(bookID AS INT)";
                stm = con.prepareStatement(sql);
            } else {
                //sql = "SELECT * FROM Books WHERE ";
            }

            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
//                JAXBContext ctx = JAXBContext.newInstance(BookListDTO.class);
//                Marshaller mar = ctx.createMarshaller();
//                mar.setProperty(Marshaller.JAXB_ENCODING, "UTF-8");
//                mar.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
//                BookListDTO listBook = new BookListDTO();
                BookDTO bookTemp = new BookDTO();
                bookTemp = new BookDTO();
                bookTemp.setBookID(rs.getString(1));
                bookTemp.setTitle(rs.getString(2));
                bookTemp.setAuthor(rs.getString(3));
                bookTemp.setPublisher(rs.getString(4));
                bookTemp.setDescription(rs.getString(5));
                bookTemp.setPrice(rs.getFloat(6));
                bookTemp.setInventory(rs.getInt(7));
                bookTemp.setImageLink(rs.getString(8));
                bookTemp.setDateModify(new java.util.Date(rs.getTimestamp(9).getTime()));
                bookTemp.setCategory(rs.getString(10));
                bookTemp.setBestseller(rs.getBoolean(11));
                result.add(bookTemp);
//                listBook.getBookList().add(bookTemp);
//                mar.marshal(listBook, new File("C:\\books.xml"));
            }
            stm.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }

    public static int insert(Connection con, BookDTO book) {
        int result = 0;
        try {
            String sql = "INSERT INTO Books VALUES(?,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, book.getBookID());
            stm.setString(2, book.getTitle());
            stm.setString(3, book.getAuthor());
            stm.setString(4, book.getPublisher());
            stm.setString(5, book.getDescription());
            stm.setFloat(6, book.getPrice());
            stm.setInt(7, book.getInventory());
            stm.setString(8, book.getImageLink());
            stm.setTimestamp(9, new java.sql.Timestamp(book.getDateModify().getTime()));
            stm.setString(10, book.getCategory());
            stm.setBoolean(11, book.isBestseller());
            result = stm.executeUpdate();

            stm.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static int update(Connection con, BookDTO book) {
        int result = 0;
        try {
            String query = "UPDATE Books SET title=?, author=?, "
                    + "publisher=?, description=?, price=?, inventory=?, image=?, "
                    + "dateModify=?, category=?, bestseller=? "
                    + "WHERE bookID=?";
            PreparedStatement stm = con.prepareStatement(query);
            stm.setString(1, book.getTitle());
            stm.setString(2, book.getAuthor());
            stm.setString(3, book.getPublisher());
            stm.setString(4, book.getDescription());
            stm.setFloat(5, book.getPrice());
            stm.setInt(6, book.getInventory());
            stm.setString(7, book.getImageLink());
            stm.setTimestamp(8, new java.sql.Timestamp(book.getDateModify().getTime()));
            stm.setString(9, book.getCategory());
            stm.setBoolean(10, book.isBestseller());
            stm.setString(11, book.getBookID());

            result = stm.executeUpdate();
            stm.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static int delete(Connection con, String bookID) {
        int result = 0;
        try {
            String sql = "DELETE FROM Books WHERE bookID=?";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, bookID);
            result = stm.executeUpdate();
            stm.close();
            //con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static ArrayList getNewestBooks(Connection con) {
        ArrayList<BookDTO> result = new ArrayList<BookDTO>();
        String sql = "";
        PreparedStatement stm = null;
        try {

            sql = "SELECT TOP(6) * FROM Books ORDER BY dateModify DESC";
            stm = con.prepareStatement(sql);


            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
//                JAXBContext ctx = JAXBContext.newInstance(BookListDTO.class);
//                Marshaller mar = ctx.createMarshaller();
//                mar.setProperty(Marshaller.JAXB_ENCODING, "UTF-8");
//                mar.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
//                BookListDTO listBook = new BookListDTO();
                BookDTO bookTemp = new BookDTO();
                bookTemp = new BookDTO();
                bookTemp.setBookID(rs.getString(1));
                bookTemp.setTitle(rs.getString(2));
                bookTemp.setAuthor(rs.getString(3));
                bookTemp.setPublisher(rs.getString(4));
                bookTemp.setDescription(rs.getString(5));
                bookTemp.setPrice(rs.getFloat(6));
                bookTemp.setInventory(rs.getInt(7));
                bookTemp.setImageLink(rs.getString(8));
                bookTemp.setDateModify(new java.util.Date(rs.getTimestamp(9).getTime()));
                bookTemp.setCategory(rs.getString(10));
                bookTemp.setBestseller(rs.getBoolean(11));
                result.add(bookTemp);
//                listBook.getBookList().add(bookTemp);
//                mar.marshal(listBook, new File("C:\\books.xml"));
            }
            stm.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }

    public static String generateID(Connection con) {
        String id = null;
        try {
            String sql = "SELECT MAX(numericID) "
                    + "FROM (SELECT 'numericID' = "
                    + "CASE WHEN ISNUMERIC(bookID) = '1' THEN CAST(bookID AS INT) "
                    + "ELSE NULL END FROM Books) ALIAS";
            PreparedStatement pre = con.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                int iID = Integer.parseInt(id = rs.getString(1));
                id = String.valueOf(iID + 1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return id;
    }
}
